﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace QuartzScheduler
{
    public class SqlServerClass
    {   //定义一个公用成员
        public SqlConnection conn;
        public SqlServerClass()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        #region 建立数据库连接
        public void OpenConn()
        {
            String strconn = System.Configuration.ConfigurationManager.AppSettings["sqlconn"].ToString();
            conn = new SqlConnection(strconn);
            if (conn.State.ToString().ToLower() == "open")
            {
                //连接为打开时
            }
            else
            {
                //连接为关闭时
                conn.Open();
            }
        }
        #endregion
        #region 关闭并释放连接
        public void CloseConn()
        {
            if (conn.State.ToString().ToLower() == "open")
            {
                //连接为打开时
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion
        #region 返回DataReader，用于读取数据
        public SqlDataReader DataRead(string sql)
        {
            OpenConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }
        #endregion
        #region 返回一个数据集
        public DataSet MySqlDataSet(string Sql, string tableName)
        {
            OpenConn();
            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(Sql, conn);
            da.Fill(ds, tableName);
            CloseConn();
            return ds;
        }
        #endregion
        //返回一个数据集
        public DataView MySqlDataSource(string Sql)
        {
            OpenConn();
            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(Sql, conn);
            da.Fill(ds, "temp");
            CloseConn();
            return ds.Tables[0].DefaultView;
        }
        #region 执行一个SQL操作:添加、删除、更新操作
        //执行一个SQL操作:添加、删除、更新操作
        public void MySqlExcute(string sql)
        {
            OpenConn();
            SqlCommand cmd;
            cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            CloseConn();
        }
        #endregion
        #region 执行一个SQL操作:添加、删除、更新操作，返回受影响的行
        //执行一个SQL操作:添加、删除、更新操作，返回受影响的行
        public int MySqlExecuteNonQuery(string sql)
        {
            OpenConn();
            SqlCommand cmd;
            cmd = new SqlCommand(sql, conn);
            int flag = cmd.ExecuteNonQuery();
            return flag;
        }
        #endregion
        public object MySqlExecuteScalar(string sql)
        {
            OpenConn();
            SqlCommand cmd;
            cmd = new SqlCommand(sql, conn);
            object obj = cmd.ExecuteScalar();
            cmd.Dispose();
            CloseConn();
            return obj;
        }
        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable MySqlDataTable(string sql)
        {
            OpenConn();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(ds, "table");
            CloseConn();
            return ds.Tables["table"];
        }
        /// <summary>
        /// 返回一个数据集的记录数
        /// </summary>
        /// <param name="sql">传递的sql语句必须为一个统计查询</param>
        /// <returns></returns>
        public int MySqlRecordCount(string sql)
        {
            //注：Sql 语句必须是一个统计查询
            OpenConn();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            int RecordCount = -1;
            while (dr.Read())
            {
                RecordCount = int.Parse(dr[0].ToString());
            }
            CloseConn();
            return RecordCount;
        }
    }
}